package saleMarket.dao;

import saleMarket.domain.FreightCargo;
import saleMarket.service.*;
import util.JdbcHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.TreeSet;

public final class FreightCargoDao {
    private static FreightCargoDao freightCargoDao = new FreightCargoDao();

    private FreightCargoDao() {
    }

    public static FreightCargoDao getInstance() {
        return freightCargoDao;
    }

    public static Collection<FreightCargo> findAll( Connection connection)
            throws SQLException {
        //创建集合类对象，用来保存所有记录代表的对象
        Collection<FreightCargo> freightCargo = new ArrayList<FreightCargo>();
        //准备预编译的语句
        int totalNum = CommonDao.getInstance().count(connection, "FreightCargo");
        //创建查询的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM FreightCargo ");
        //执行预编译语句，结果集放在resultSet中
        PreparedStatement pstmt = connection.prepareStatement(sql_select.toString());
        ResultSet resultSet = pstmt.executeQuery();
        //遍历resultSet
        //id,freightStatus,inventoryGoods_id,logistics_id
        while (resultSet.next()) {
            freightCargo.add(new FreightCargo(resultSet.getInt("id"),
                    resultSet.getString("description"),
                    InventoryGoodsService.getInstance().find(resultSet.getInt("inventoryGoods_id")),
                    LogisticsService.getInstance().find(resultSet.getInt("logistics_id"))
            ));
        }
        JdbcHelper.close(resultSet, pstmt);
        return freightCargo;
    }

    public static FreightCargo find(int id, Connection connection) throws SQLException {
        FreightCargo freightCargo = null;
        //创建sql语句
        String find_sql = "select * from FreightCargo where id = ?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(find_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //根据id查找
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            freightCargo = new FreightCargo(resultSet.getInt("id"),
                    resultSet.getString("description"),
                    InventoryGoodsService.getInstance().find(resultSet.getInt("inventoryGoods_id")),
                    LogisticsService.getInstance().find(resultSet.getInt("logistics_id"))
            );
        }
        JdbcHelper.close(resultSet, pstmt);
        return freightCargo;
    }

    public static boolean update(FreightCargo freightCargo,
                                 Connection connection) throws SQLException {
        //创建sql语句
        //id,freightStatus,inventoryGoods_id,logistics_id
        String updateGPC_sql = "update FreightCargo set " +
                "description = ?,inventoryGoods_id = ?,logistics_id = ? " +
                "where id = ?";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateGPC_sql);
        //为预编译参数赋值
        pstmt.setInt(4, freightCargo.getId());
        pstmt.setString(1, freightCargo.getDescription());
        pstmt.setInt(2, freightCargo.getInventoryGoods().getId());
        pstmt.setInt(3, freightCargo.getLogistics().getId());
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null, pstmt);
        return affectedRowNum > 0;
    }

    public static Integer add(FreightCargo freightCargo, Connection connection) throws SQLException {
        //创建sql语句
        String addGPC_sql = "insert into FreightCargo (description,inventoryGoods_id,logistics_id) " +
                        "values (?,?,?)";
        //在该链接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(addGPC_sql,Statement.RETURN_GENERATED_KEYS);
        //为预编译参数赋值
        pstmt.setString(1,freightCargo.getDescription());
        pstmt.setInt(2, freightCargo.getInventoryGoods().getId());
        pstmt.setInt(3, freightCargo.getLogistics().getId());
        int idOfAdded= 0;
        int affectedRowNum = pstmt.executeUpdate();
        //获取新增对象id并为applicationForSupervisor赋值
        ResultSet resultSet = pstmt.getGeneratedKeys();
        if (resultSet.next()) {
            //读取第一个
            idOfAdded = resultSet.getInt(1);
        }
        //关闭连接
        JdbcHelper.close(null, pstmt);
        return idOfAdded ;
    }

    public static boolean delete(int id, Connection connection) throws SQLException {
        //创建sql语句
        String deleteGPC_sql = "DELETE FROM FreightCargo WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(deleteGPC_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //获取执行结果
        int affectedRowNum = pstmt.executeUpdate();
        //关闭连接
        JdbcHelper.close(null, pstmt);
        return affectedRowNum > 0 ;
    }
}

